﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using net.xBei.Helper;
using net.xBei.Models.FilterCondition;
using Microsoft.EntityFrameworkCore;
using MySqlConnector;
using System.Linq.Expressions;
using net.xBei.Models.Request;

namespace net.xBei.Models.Database; 
/// <summary>
/// 
/// </summary>
public class TableQuery : IToSql {
    private readonly string alias;
    private int aliasCount = 1;
    internal string Table { get; }
    /// <summary>
    /// 
    /// </summary>
    public int Size { get; private set; } = 0;
    /// <summary>
    /// 
    /// </summary>
    public int Skip { get; private set; } = 0;
    private List<JoinTable> JoinTables { get; set; }
    private List<MySqlParameter> parameters { get; set; }
    private List<string> groupByList { get; set; }
    private List<KeyValuePair<string, string>> orderList { get; set; }
    /// <summary>
    /// 
    /// </summary>
    public MySqlParameter[] Parameters => [.. parameters];
    private List<string> where { get; set; }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="table"></param>
    /// <param name="alias"></param>
    public TableQuery(string table, string alias = "") {
        Table = Factory.FixSqlKeyName(table);
        this.alias = Factory.FixSqlKeyName(string.IsNullOrWhiteSpace(alias) ? "A1" : alias);
        aliasCount = 2;
        where = [];
        JoinTables = [];
        parameters = [];
        orderList = [];
        groupByList = [];
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="table"></param>
    /// <param name="alias"></param>
    /// <returns></returns>
    public static TableQuery Create(string table, string alias = "") => new(table, alias);
    /// <summary>
    /// 
    /// </summary>
    /// <param name="size"></param>
    /// <param name="skip"></param>
    /// <returns></returns>
    public TableQuery Limit(int size, int skip = 0) {
        Size = Math.Max(1, size);
        Skip = Math.Max(0, skip);
        return this;
    }

    #region 拼接条件
    /// <summary>
    /// 传入多个条件（OR）
    /// </summary>
    /// <param name="conditions"></param>
    /// <returns></returns>
    public TableQuery WhereOr(params string[] conditions)
        => Where(conditions.Select(s => $"({s})").JoinBy("\r\n\t OR "));
    /// <summary>
    /// 
    /// </summary>
    /// <param name="conditions"></param>
    /// <param name="param"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public TableQuery Where(string conditions, string param, object value) => Where(conditions, new MySqlParameter(param, value));
    /// <summary>
    /// 
    /// </summary>
    /// <param name="conditions"></param>
    /// <param name="parameter"></param>
    /// <returns></returns>
    public TableQuery Where(string conditions, MySqlParameter parameter) {
        where.Add($"({conditions})");
        if (!parameters.Any(p => p.ParameterName == parameter.ParameterName)) {
            parameters.Add(parameter);
        }
        return this;
    }
    /// <summary>
    /// 新增条件（默认是AND）`FIELD`=@VALUE
    /// </summary>
    /// <param name="field">字段</param>
    /// <param name="value">值</param>
    /// <returns></returns>
    public TableQuery Where(string field, object value) {
        var p = Factory.GetParameterName(parameters);
        parameters.Add(new MySqlParameter(p, value));
        where.Add($"{Factory.FixSqlField(field)} = @{p}");
        return this;
    }
    /// <summary>
    /// WHERE 
    /// </summary>
    /// <param name="conditions"></param>
    /// <returns></returns>
    public TableQuery Where(string conditions) {
        if (where.Contains(conditions)) return this;
        where.Add($"({conditions})");
        return this;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="subTableBuilder"></param>
    /// <returns></returns>
    public TableQuery WhereExistsSubTable(Func<TableQuery, string> subTableBuilder) {
        where.Add($"EXISTS({subTableBuilder.Invoke(this)})");
        return this;
    }
    ///// <summary>
    ///// WHERE FIELD IN(SELECT FIELD FROM SubTable Where)
    ///// </summary>
    ///// <typeparam name="T"></typeparam>
    ///// <param name="subTableBuilder"></param>
    ///// <param name="field"></param>
    ///// <param name="values"></param>
    ///// <returns></returns>
    //public TableQuery WhereInSubTable<T>(Func<IEnumerable<MySqlParameter>, string> subTableBuilder, string field, IEnumerable<T> values) {
    //    if (!values.Any()) {
    //        throw new Exception("条件值不能为空");
    //    };
    //    var plist = Factory.GetArray(values, parameters);
    //    var fixField = Factory.FixSqlField(field);
    //    where.Add($"{fixField} IN ({subTableBuilder.Invoke(plist)})");
    //    return this;
    //}
    /// <summary>
    /// WHERE FIELD IN(v1, v2 ...)
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="field"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public TableQuery WhereIn<T>(string field, IEnumerable<T> values) {
        if (!values.Any()) {
            values = [];
        };
        var plist = Factory.GetArray(values, parameters);
        var fixField = Factory.FixSqlField(field);
        where.Add($"({fixField} IN ({plist.Select(p => $"@{p.ParameterName}").JoinBy(",")}))");
        return this;
    }
    /// <summary>
    /// WHERE FIELD NOT IN(v1, v2 ...)
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="field"></param>
    /// <param name="values"></param>
    /// <returns></returns>
    public TableQuery WhereNotIn<T>(string field, IEnumerable<T> values) {
        if (!values.Any()) {
            values = [];
        };
        var plist = Factory.GetArray(values, parameters);
        var fixField = Factory.FixSqlField(field);
        where.Add($"({fixField} NOT IN ({plist.Select(p => $"@{p.ParameterName}").JoinBy(",")}))");
        return this;
    }
    /// <summary>
    /// WHERE FIELD = value
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="values"></param>
    /// <returns></returns>
    public IEnumerable<MySqlParameter> GetArray<T>(IEnumerable<T> values) => Factory.GetArray(values, parameters);
    /// <summary>
    /// WHERE FIELD LIKE value
    /// </summary>
    /// <param name="field"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public TableQuery WhereLike(string field, string value) {
        var p = Factory.AddParameter(value, parameters);
        return Where($"({Factory.FixSqlField(field)} LIKE @{p.ParameterName})");
    }
    /// <summary>
    /// WHERE FIELD BETWEEN v1 AND v2
    /// </summary>
    /// <param name="field"></param>
    /// <param name="begin"></param>
    /// <param name="end"></param>
    /// <returns></returns>
    public TableQuery WhereBetween(string field, int begin, int end) {
        var pBegin = Factory.AddParameter(begin, parameters);
        var pEnd = Factory.AddParameter(end, parameters);
        return Where($"({Factory.FixSqlField(field)} BETWEEN @{pBegin.ParameterName} AND @{pEnd.ParameterName})");
    }
    /// <summary>
    /// WHERE FIELD BETWEEN v1 AND v2
    /// </summary>
    /// <param name="field"></param>
    /// <param name="begin"></param>
    /// <param name="end"></param>
    /// <returns></returns>
    public TableQuery WhereBetween(string field, decimal begin, decimal end) {
        var pBegin = Factory.AddParameter(begin, parameters);
        var pEnd = Factory.AddParameter(end, parameters);
        return Where($"({Factory.FixSqlField(field)} BETWEEN @{pBegin.ParameterName} AND @{pEnd.ParameterName})");
    }
    /// <summary>
    /// WHERE FIELD BETWEEN v1 AND v2
    /// </summary>
    /// <param name="field"></param>
    /// <param name="begin"></param>
    /// <param name="end"></param>
    /// <returns></returns>
    public TableQuery WhereBetween(string field, DateTime begin, DateTime end) {
        var pBegin = Factory.AddParameter(begin, parameters);
        var pEnd = Factory.AddParameter(end, parameters);
        return Where($"({Factory.FixSqlField(field)} BETWEEN @{pBegin.ParameterName} AND @{pEnd.ParameterName})");
    }
    #endregion

    #region 表连接
    /// <summary>
    /// JOIN
    /// </summary>
    /// <param name="table"></param>
    /// <param name="alias"></param>
    /// <param name="conditions"></param>
    /// <returns></returns>
    public TableQuery Join(string table, string alias, string conditions) => Join(table, alias, conditions, JoinTableMode.INNER);
    /// <summary>
    /// LEFT JOIN
    /// </summary>
    /// <param name="table"></param>
    /// <param name="alias"></param>
    /// <param name="conditions"></param>
    /// <returns></returns>
    public TableQuery LeftJoin(string table, string alias, string conditions) => Join(table, alias, conditions, JoinTableMode.LEFT);
    /// <summary>
    /// RIGHT JOIN 
    /// </summary>
    /// <param name="table"></param>
    /// <param name="alias"></param>
    /// <param name="conditions"></param>
    /// <returns></returns>
    public TableQuery RightJoin(string table, string alias, string conditions) => Join(table, alias, conditions, JoinTableMode.RIGHT);
    /// <summary>
    /// JOIN
    /// </summary>
    /// <param name="table"></param>
    /// <param name="alias"></param>
    /// <param name="conditions"></param>
    /// <param name="mode"></param>
    /// <returns></returns>
    public TableQuery Join(string table, string alias, string conditions, JoinTableMode mode) {
        table = Factory.FixSqlKeyName(table);
        alias = Factory.FixSqlKeyName(string.IsNullOrWhiteSpace(alias) ? $"A{aliasCount++}" : alias);
        if (JoinTables.Any(jt => jt.Table == table && jt.Alias == alias)) return this;
        JoinTables.Add(new JoinTable(table, alias, mode, conditions));
        return this;
    }
    #endregion

    #region 排序
    /// <summary>
    /// 倒序
    /// </summary>
    /// <param name="field">如果重复会删除之前的</param>
    /// <returns></returns>
    public TableQuery OrderByDescending(string field) {
        field = Factory.FixSqlField(field);
        //if (orderList.Any(kv => kv.Key == field)) {
        //    orderList.Where(kv => kv.Key != field);
        //}
        orderList.Add(new KeyValuePair<string, string>(field, "DESC"));
        return this;
    }
    #endregion

    #region 分组
    /// <summary>
    /// GROUP BY
    /// </summary>
    /// <param name="field"></param>
    /// <returns></returns>
    public TableQuery GroupBy(string field) {
        field = Factory.FixSqlField(field);
        groupByList.Add(field);
        return this;
    }
    #endregion
    #region 排序
    /// <summary>
    /// 
    /// </summary>
    /// <param name="field"></param>
    /// <returns></returns>
    public TableQuery OrderBy(string field) {
        field = Factory.FixSqlField(field);
        if (orderList.Any(kv => kv.Key == field)) {
            orderList.Where(kv => kv.Key != field);
        }
        orderList.Add(new KeyValuePair<string, string>(field, "ASC"));
        return this;
    }
    /// <summary>
    /// 随机
    /// </summary>
    /// <returns></returns>
    public TableQuery Rand() {
        orderList.Add(new KeyValuePair<string, string>("RAND()" , "ASC"));
        return this;
    }
    #endregion
    #region 获取数据
    /// <summary>
    /// SELECT COUNT(DISTINCT Field)
    /// <see cref="ToCountSql(string, bool, int)"/>，<see cref="RelationalDatabaseFacadeExtensions.SqlQueryRaw{TResult}(Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade, string, object[])"/>
    /// </summary>
    /// <param name="database"></param>
    /// <param name="field"></param>
    /// <returns></returns>
    public async Task<int> CountAsync(Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade database, string field) {
        return await database.SqlQueryRaw<int>(ToCountSql(field).Replace("`COUNT`", "Value"), Parameters).SingleAsync();
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    /// <typeparam name="TKey"></typeparam>
    /// <param name="dbSet"></param>
    /// <param name="orderBy">默认是降序</param>
    /// <param name="search"></param>
    /// <returns></returns>
    public async Task<TEntity[]> GetListAsync<TEntity, TKey>(DbSet<TEntity> dbSet, Expression<Func<TEntity, TKey>> orderBy, SearchRequest search)
        where TEntity : class {
        return await dbSet.FromSqlRaw(ToSql(), Parameters)
            .OrderByDescending(orderBy)
            .Skip(search.Skip())
            .Take(search.GetLimit())
            .ToArrayAsync();
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    /// <typeparam name="TKey"></typeparam>
    /// <param name="dbSet"></param>
    /// <param name="fields"></param>
    /// <param name="orderBy"></param>
    /// <param name="search"></param>
    /// <returns></returns>
    public async Task<TEntity[]> GetListAsync<TEntity, TKey>(DbSet<TEntity> dbSet,
                                                             IEnumerable<string> fields,
                                                             Expression<Func<TEntity, TKey>> orderBy,
                                                             SearchRequest search)
        where TEntity : class {
        return await dbSet.FromSqlRaw(ToSql(fields), Parameters)
            .OrderByDescending(orderBy)
            .Skip(search.Skip())
            .Take(search.GetLimit())
            .ToArrayAsync();
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    /// <param name="dbSet"></param>
    /// <param name="fields"></param>
    /// <param name="format"></param>
    /// <param name="indent"></param>
    /// <returns></returns>
    public IQueryable<TEntity> Query<TEntity>(DbSet<TEntity> dbSet,
                                              IEnumerable<string> fields,
                                              bool format = false,
                                              int indent = 0) where TEntity : class {
//#if DEBUG
//            Console.WriteLine();
//            Console.WriteLine(ToSql(fields, true, indent));
//            Console.WriteLine(parameters.Select(FormatParameter).ToJson(true));
//            Console.WriteLine();
//#endif
        return dbSet.FromSqlRaw(ToSql(fields, format, indent), Parameters);
    }
    #endregion
    static string FormatParameter(MySqlParameter p) {
        if (p.Value is DateTime) {
            return $"@{p.ParameterName} = \'{p.Value}\'";
        }
        if (p.Value is string) {
            return $"@{p.ParameterName} = \'{p.Value}\'";
        }
        return $"@{p.ParameterName} = {p.Value}";
    }
    #region 合成 Sql
    /// <summary>
    /// 
    /// </summary>
    /// <param name="fields"></param>
    /// <param name="format"></param>
    /// <param name="indent"></param>
    /// <returns></returns>
    public string ToSql(string fields, bool format = false, int indent = 0) {
        if (fields == "*") return ToSql(format, indent);
        return ToSql(fields.Contains(',') ? fields.Split(',') : new[] { fields }, format, indent);
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="fields"></param>
    /// <param name="format"></param>
    /// <param name="indent"></param>
    /// <returns></returns>
    public string ToSql(IEnumerable<string> fields, bool format = false, int indent = 0) {
        if (fields.Any() == false) return ToSql(format, indent);
        var sb = new StringBuilder();
        sb.AppendLine("SELECT DISTINCT ");
        sb.Append(GetIndent(indent + 1));
        if (format) {
            sb.AppendLine(fields.Select(Factory.FixSelectField).JoinBy(", "));
        } else {
            sb.AppendLine(fields.Select(Factory.FixSelectField).JoinBy(",\r\n"));
        }
        sb.AppendLine($"FROM {Table} {alias}");
        if (JoinTables.Count != 0) {
            if (format) {
                sb.Append(JoinTables.Select(jt => jt.ToSql(format, indent + 1)).JoinBy("\r\n"));
            } else {
                sb.Append(JoinTables.Select(jt => jt.ToSql(format, indent + 1)).JoinBy("\r\n"));
            }
        }
        if (where.Count != 0) {
            sb.AppendLine("\r\nWHERE ");
            sb.AppendLine(where.JoinBy($"\r\n {GetIndent(indent + 1)}AND "));
        }
        if (groupByList.Count != 0) {
            sb.AppendLine("\r\nGROUP BY ");
            sb.AppendLine(groupByList.JoinBy(","));
        }
        if (orderList.Count != 0) {
            sb.AppendLine("\r\nORDER BY ");
            sb.AppendLine(orderList.Select(kv => $"{kv.Key} {kv.Value}").JoinBy(","));
        }
        if (Size > 0 && Skip > 0) {
            sb.AppendLine($"LIMIT {Size} OFFSET {Skip}");
        } else if (Size > 0) {
            sb.AppendLine($"LIMIT {Size}");
        } else if (Skip > 0) {
            sb.AppendLine($"OFFSET {Skip}");
        }
        return sb.ToString();
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="field"></param>
    /// <param name="format"></param>
    /// <param name="indent"></param>
    /// <returns></returns>
    public string ToCountSql(string field, bool format = false, int indent = 0) {
        var sb = new StringBuilder();
        sb.AppendLine($"SELECT COUNT(DISTINCT {Factory.FixSqlField(field)}) `COUNT` ");
        sb.AppendLine($"FROM {Table} {alias}");
        if (JoinTables.Count != 0) {
            if (format) {
                sb.Append(JoinTables.Select(jt => jt.ToSql(format, indent + 1)).JoinBy("\r\n"));
            } else {
                sb.Append(JoinTables.Select(jt => jt.ToSql(format, indent + 1)).JoinBy(" "));
            }
        }
        if (where.Count != 0) {
            sb.AppendLine("\r\nWHERE ");
            sb.Append(where.JoinBy($"\r\n {GetIndent(indent + 1)}AND "));
        }
        if (groupByList.Count != 0) {
            sb.AppendLine("\r\nGROUP BY ");
            sb.AppendLine(groupByList.JoinBy(","));
        }
        return sb.ToString();
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="format"></param>
    /// <param name="indent"></param>
    /// <returns></returns>
    public string ToSql(bool format = false, int indent = 0) {
        var sb = new StringBuilder();
        sb.AppendLine("SELECT * ");
        sb.AppendLine($"FROM {Table} {alias}");
        if (JoinTables.Count != 0) {
            if (format) {
                sb.Append(JoinTables.Select(jt => jt.ToSql(format, indent + 1)).JoinBy("\r\n"));
            } else {
                sb.Append(JoinTables.Select(jt => jt.ToSql(format, indent + 1)).JoinBy(" "));
            }
        }
        if (where.Count != 0) {
            sb.AppendLine("\r\nWHERE ");
            sb.Append(where.JoinBy($"\r\n {GetIndent(indent + 1)}AND "));
        }
        if (groupByList.Count != 0) {
            sb.AppendLine("\r\nGROUP BY ");
            sb.AppendLine(groupByList.JoinBy(","));
        }
        if (orderList.Count != 0) {
            sb.AppendLine("\r\nORDER BY ");
            sb.AppendLine(orderList.Select(kv => $"{kv.Key} {kv.Value}").JoinBy(","));
        }
        if (Size > 0 && Skip > 0) {
            sb.AppendLine($"LIMIT {Size} OFFSET {Skip}");
        } else if (Size > 0) {
            sb.AppendLine($"LIMIT {Size}");
        } else if (Skip > 0) {
            sb.AppendLine($"OFFSET {Skip}");
        }
        return sb.ToString();
    }
    #endregion
    /// <summary>
    /// 
    /// </summary>
    /// <param name="name"></param>
    /// <param name="v"></param>
    /// <returns></returns>
    public TableQuery AddParameter(string name, object v)
        => AddParameter(new MySqlParameter(name, v));
    /// <summary>
    /// 
    /// </summary>
    /// <param name="p"></param>
    /// <returns></returns>
    public TableQuery AddParameter(MySqlParameter p) {
        if (parameters.Any(pp => pp.ParameterName == p.ParameterName)) {
            //throw new Exception($"重复添加参数：（{p.ParameterName}）");
            return this;
        }
        parameters.Add(p);
        return this;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="list"></param>
    /// <returns></returns>
    public IEnumerable<MySqlParameter> AddWhereInParameters<T>(IEnumerable<T> list) {
        return Factory.GetArray(list, parameters);
    }
    internal static string GetIndent(int indent, char paddingChar = '\t') => indent < 1 ? string.Empty : "".PadLeft(indent, paddingChar);
}
/// <summary>
/// 
/// </summary>
public struct JoinTable {
    /// <summary>
    /// 
    /// </summary>
    public string Table { get; private set; }
    /// <summary>
    /// 
    /// </summary>
    public string Alias { get; private set; }
    /// <summary>
    /// 
    /// </summary>
    public JoinTableMode Mode { get; private set; }
    /// <summary>
    /// 
    /// </summary>
    public string Conditions { get; private set; }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="table"></param>
    /// <param name="alias"></param>
    /// <param name="mode"></param>
    /// <param name="conditions"></param>
    public JoinTable(string table, string alias, JoinTableMode mode, string conditions) {
        Table = table;
        Alias = alias;
        Mode = mode;
        Conditions = conditions;
    }
    /// <summary>
    /// 
    /// </summary>
    /// <param name="format"></param>
    /// <param name="indent"></param>
    /// <returns></returns>
    public string ToSql(bool format = false, int indent = 0) {
        return $"{TableQuery.GetIndent(indent)}{Mode} JOIN {Table} {Alias} ON {Conditions}";
    }
}
/// <summary>
/// 表连接模式
/// </summary>
public enum JoinTableMode {
    /// <summary>
    /// 
    /// </summary>
    INNER,
    /// <summary>
    /// 
    /// </summary>
    LEFT,
    /// <summary>
    /// 
    /// </summary>
    RIGHT
}
/// <summary>
/// 
/// </summary>
public interface IToSql {
    /// <summary>
    /// 
    /// </summary>
    /// <param name="format"></param>
    /// <param name="indent"></param>
    /// <returns></returns>
    string ToSql(bool format = false, int indent = 0);
}
